#!/usr/bin/env bash
#抽取DWD层行程数据到DWS层dws_area_vehicle_driving_statistics_pdi表

CUR_DIR=$(cd "$(dirname "${BASH_SOURCE-$0}")"; pwd)
UP_DIR=$(dirname $(dirname $(dirname "$PWD")))
echo ${UP_DIR}
source ${UP_DIR}/system_env.sh


START_TIME=$(date +%s)
hive_db=${hive_db}
hive_db_source=${hive_db_source}

if [ -z "${hive_db}" ];then
  hive_db=dataplatform_trip
else
  hive_db=${hive_db}
fi

if [ -z "${hive_db_source}" ];then
  hive_db_source=dongfeng
else
  hive_db_source=${hive_db_source}
fi


if [ -z "$1" ];then
  part_time=$(date -d "1 days ago" +%Y%m%d)
else
  part_time=$1
fi

#生成hql语句
sql_suffix=$(cat <<EOF
insert overwrite table ${hive_db}.dws_area_vehicle_driving_statistics_pdi partition (day=${part_time})
select src2.day as trip_date
        ,area.area_name
        ,sum(next_city_mileage - city_end_mileage) as total_area_mileage
        ,sum(next_city_fuel - city_end_fuel) as total_area_mileage
        ,concat_ws(',',collect_set(cast(src2.tid as string))) as tid_list
  from (
        select src1.tid
                ,get_json_object(city_json,'$.lcid') as city_id
                ,get_json_object(city_json,'$.stime') as city_start_time
                ,get_json_object(city_json,'$.mileage') as city_end_mileage
                ,get_json_object(city_json,'$.fuel') as city_end_fuel
                ,LEAD(get_json_object(city_json,'$.mileage'),1,src1.route_end_mileage) over (partition by src1.tid,src1.trip_id order by get_json_object(city_json,'$.stime')) as next_city_mileage
                ,LEAD(get_json_object(city_json,'$.fuel'),1,src1.route_end_fuel) over (partition by src1.tid,src1.trip_id order by get_json_object(city_json,'$.stime')) as next_city_fuel
                ,day
            from (select tid
                        ,trip_id
                        ,route_start_time
                        ,route_end_mileage
                        ,route_end_fuel
                        ,cross_cities_detail
                        ,day
                from ${hive_db}.trip_statistics
                where cross_cities_detail != '[]'
                  and day = ${part_time}
             ) src1
            LATERAL VIEW explode(split(regexp_replace(regexp_replace(cross_cities_detail, '\\\\}\\\\,\\\\{','\\\\}\\\\;\\\\{'),'\\\\[|\\\\]',''),'\\\\;')) json as city_json
    ) src2
  join (select area_name,city_code from ${hive_db}.dim_pub_area group by area_name,city_code) area
    on src2.city_id = area.city_code
 group by area.area_name,src2.day
EOF
)

CUR_DIR=$(cd "$(dirname "$0")"; pwd)
hive_config=$(cat  ${CUR_DIR}/hive.config)
sql="${hive_config}${sql_suffix}"
echo "导入dws_area_vehicle_driving_statistics_pdi的SQL为：${sql}"

#执行语句
hive -e "${sql}"
if [[ $? -ne 0 ]];then
  echo "导入dws_area_vehicle_driving_statistics_pdi失败，耗时$((`date +%s`-START_TIME))秒..."
else
  echo "导入dws_area_vehicle_driving_statistics_pdi成功，耗时$((`date +%s`-START_TIME))秒..."
fi